﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using DAL;
using Entities;
using Excel = Microsoft.Office.Interop.Excel;
using Aron.Sinoai.OfficeHelper;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;


namespace BLL
{
    public class BLL_Order
    {
        private DAL_Order dal_Order = new DAL_Order();

        public List<Order> Delete_Order_FromList(List<Order> pLstOrder, Order pOrder)
        {
            int intIndex = -1;
            for (int i = 0; i < pLstOrder.Count; i++)
            {
                if (pLstOrder[i].ID_PRODUCT == pOrder.ID_PRODUCT)
                {
                    intIndex = i;
                    break;
                }
            }
            if (intIndex != -1)
            {
                pLstOrder.RemoveAt(intIndex);
            }
            return pLstOrder;
        }

        public List<Order> Delete_Order_FromList(List<Order> pLstOrder, Int64 pID_PRODUCT)
        {
            int intIndex = -1;
            for (int i = 0; i < pLstOrder.Count; i++)
            {
                if (pLstOrder[i].ID_PRODUCT == pID_PRODUCT)
                {
                    intIndex = i;
                    break;
                }
            }
            if (intIndex != -1)
            {
                pLstOrder.RemoveAt(intIndex);
            }
            return pLstOrder;
        }
        
        public string Get_Order_Code()
        {
            return dal_Order.Get_Order_Code();
        }
        
        public DataTable Get_Order_All()
        {
            return dal_Order.Get_Order_All();
        }
        
        public DataTable Get_OrderList(string pStatus)
        {
            return dal_Order.Get_OrderList(pStatus);
        }
        
        public DataTable Get_Order(Int64 pID)
        {
            return dal_Order.Get_Order(pID);
        }
        
        public DataTable Get_Order_OrderCode(string pOrderCode)
        {
            return dal_Order.Get_Order_OrderCode(pOrderCode);
        }
        
        public DataTable Get_Order_Dynamic(string pCondition)
        {
            return dal_Order.Get_Order_Dynamic(pCondition);
        }
        
        public List<Order> Lst_Order(DataTable pdt)
        {
            List<Order> lstReturn = new List<Order>();
            BLL_Product bll_Product = new BLL_Product();
            if (pdt != null && pdt.Rows.Count > 0)
            {
                foreach (DataRow dr in pdt.Rows)
                {
                    Order Order = new Order();
                    Order.ID = (dr["ID"] == DBNull.Value ? 0 : Convert.ToInt64(dr["ID"]));
                    Order.ID_PRODUCT = (dr["ID_PRODUCT"] == DBNull.Value ? 0 : Convert.ToInt64(dr["ID_PRODUCT"]));
                    Order.QUANTITY = (dr["QUANTITY"] == DBNull.Value ? 0 : Convert.ToInt64(dr["QUANTITY"]));
                    Order.PRICE = (dr["PRICE"] == DBNull.Value ? 0 : Convert.ToInt64(dr["PRICE"]));
                    Order.VALUE = (dr["VALUE"] == DBNull.Value ? 0 : Convert.ToInt64(dr["VALUE"]));
                    Order.TAX = (dr["TAX"] == DBNull.Value ? 0 : Convert.ToInt64(dr["TAX"]));
                    Order.TOTAL = (dr["TOTAL"] == DBNull.Value ? 0 : Convert.ToInt64(dr["TOTAL"]));
                    Order.FULLNAME = Convert.ToString(dr["FULLNAME"]);
                    Order.PHONE = Convert.ToString(dr["PHONE"]);
                    Order.EMAIL = Convert.ToString(dr["EMAIL"]);
                    Order.ORDER_DATE = DateTime.Now;
                    Order.RECEIVE_DATE = Convert.ToDateTime(dr["RECEIVE_DATE"]);
                    Order.ADDRESS = Convert.ToString(dr["ADDRESS"]);
                    Order.DESCRIPTION = Convert.ToString(dr["DESCRIPTION"]);
                    Order.TYPE_PAY = Convert.ToString(dr["TYPE_PAY"]);
                    Order.STATUS = Convert.ToString(dr["STATUS"]);
                    Order.CITY = Convert.ToString(dr["CITY"]);
                    Order.ORDER_CODE = Convert.ToString(dr["ORDER_CODE"]);
                    Order.PRODUCT_DETAIL = bll_Product.Lst_Product(bll_Product.Get_Product(Order.ID_PRODUCT))[0];
                    lstReturn.Add(Order);
                }
            }
            return lstReturn;
        }
        
        public List<Order> Get_AllProduct_Order()
        {
            BLL_Product bll_Product = new BLL_Product();
            List<Order> lstOrder = new List<Order>();
            List<Product> lstProduct = new List<Product>();
            lstProduct = bll_Product.Lst_Product(bll_Product.Get_Product_All_IsDisplay());
            if (lstProduct.Count > 0)
            {
                foreach (Product product in lstProduct)
                {
                    if (product.STAT_STOCK != "O" && product.STAT_STOCK != "C")
                    {
                        Order order = new Order();
                        order.PRODUCT_DETAIL = product;
                        order.PRICE = product.PRICE;
                        order.QUANTITY = 0;
                        order.STATUS = product.STAT_STOCK;
                        order.VALUE = order.PRICE * order.QUANTITY;
                        order.TOTAL = order.VALUE + order.VALUE * order.TAX;
                        order.ID_PRODUCT = product.ID;
                        lstOrder.Add(order);
                    }
                }
            }
            return lstOrder;
        }
        
        public bool InsertUpdate_Order(Order pOrder)
        {
            return dal_Order.InsertUpdate_Order(pOrder);
        }
        
        public bool InsertUpdate_ListOrder(List<Order> pListOrder)
        {
            try
            {
                foreach (Order pOrder in pListOrder)
                {
                    dal_Order.InsertUpdate_Order(pOrder);
                }
                return true;
            }
            catch
            {
                return false;
            }

        }
        
        public bool DeleteOrder(Int64 pID)
        {
            return dal_Order.DeleteOrder(pID);
        }
        
        public bool ExportToExcel(string pFileTemplate, List<Order> pLstOrder, string pFileOut)
        {
            //System.IO.MemoryStream memoryStream = new MemoryStream();
            string strFileTemplate = System.Web.HttpContext.Current.Server.MapPath(Utils.excel_directory);
            if (File.Exists(strFileTemplate))
            {
                Excel.Application objExcel = new Excel.Application();
                Excel.Workbook objWorkBook = objExcel.Workbooks.Open(strFileTemplate, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                Excel.Worksheet objWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)objWorkBook.Worksheets[1];
                Excel.Range objRange1;
                Excel.Range objRange2;

                Excel.Range objRange;
                string strValue;
                //Ngày tháng năm
                //objWorkSheet.get_Range(objWorkSheet.Cells[1,1],objWorkSheet.Cells[1,1])
                objRange1 = (Excel.Range)objWorkSheet.Cells[4, "A"];
                objRange2 = (Excel.Range)objWorkSheet.Cells[4, "G"];
                objRange = objWorkSheet.get_Range(objRange1, objRange2);
                strValue = ((object[,])(objRange.Value2))[1, 1].ToString();
                strValue = String.Format(strValue, DateTime.Now.ToString("dd"), DateTime.Now.ToString("MM"), DateTime.Now.ToString("yyyy"));
                objRange.Value2 = strValue;
                objRange.get_Characters(18, 29).Font.FontStyle = "Regular";
                objRange.get_Characters(18, 29).Font.Size = 10;
                //Khách hàng, địa chỉ, điện thoại
                objRange1 = (Excel.Range)objWorkSheet.Cells[5, "A"];
                objRange2 = (Excel.Range)objWorkSheet.Cells[5, "G"];
                objRange = objWorkSheet.get_Range(objRange1, objRange2);
                strValue = ((object[,])(objRange.Value2))[1, 1].ToString();
                strValue = String.Format(strValue, pLstOrder[0].FULLNAME, pLstOrder[0].ADDRESS, pLstOrder[0].ADDRESS);
                objRange.Value2 = strValue;
                //Danh sách chi tiết đơn hàng
                int i = 7;
                Excel.Range row;
                foreach (Order order in pLstOrder)
                {
                    objRange1 = (Excel.Range)objWorkSheet.Cells[i, "A"];
                    row = objRange1.EntireRow;
                    row.Insert(Excel.XlInsertShiftDirection.xlShiftDown, true);
                    objWorkSheet.Cells[i, "A"] = i - 6;
                    objWorkSheet.Cells[i, "B"] = order.PRODUCT_DETAIL.NAME;
                    objWorkSheet.Cells[i, "C"] = order.PRODUCT_DETAIL.WEIGHT;
                    objWorkSheet.Cells[i, "D"] = order.PRODUCT_DETAIL.PRICE;
                    objWorkSheet.Cells[i, "E"] = order.QUANTITY;
                    objWorkSheet.Cells[i, "G"] = order.TOTAL;
                    i = i + 1;
                }
                //delete row temp
                objRange1 = (Excel.Range)objWorkSheet.Cells[i, "A"];
                row = objRange1.EntireRow;
                row.Delete(Excel.XlInsertShiftDirection.xlShiftDown);
                //so luong tui
                objRange1 = (Excel.Range)objWorkSheet.Cells[i, "E"];
                objRange1.Formula = String.Format("=SUM(E{0}:E{1})", 7, i - 1);
                //total tiền
                objRange1 = (Excel.Range)objWorkSheet.Cells[i, "G"];
                objRange1.Formula = String.Format("=SUM(G{0}:G{1})", 7, i - 1);
                //
                objWorkBook.SaveCopyAs(pFileOut);
                objWorkBook.Close(false, Type.Missing, Type.Missing);
                objExcel.DisplayAlerts = false;

                objExcel.Quit();
                objExcel = null;
                return true;
            }
            else
                return false;

        }
        
        public MemoryStream ExportToExcel_NPOI(string pFileTemplate, List<Order> pLstOrder, string pFileOut)
        {
            MemoryStream output = new MemoryStream();

            try
            {
                // Opening the Excel template...
                FileStream fs = new FileStream(pFileTemplate, FileMode.Open, FileAccess.Read);
                string strValue;
                // Getting the complete workbook...
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);

                // Getting the worksheet by its name...
                //NPOI.SS.UserModel.ISheet
                NPOI.SS.UserModel.ISheet sheet = templateWorkbook.GetSheet("Sheet1");
                NPOI.SS.UserModel.IRow dataRow;
                NPOI.SS.UserModel.ICell dataCell;
                //Ngày tháng năm
                //objWorkSheet.get_Range(objWorkSheet.Cells[1,1],objWorkSheet.Cells[1,1])
                dataRow  = sheet.GetRow(3);
                dataCell = dataRow.GetCell(0);
                strValue = dataCell.ToString();
                strValue = String.Format(strValue, pLstOrder[0].RECEIVE_DATE.ToString("dd"), pLstOrder[0].RECEIVE_DATE.ToString("MM"), pLstOrder[0].RECEIVE_DATE.ToString("yyyy"));
                dataCell.SetCellValue(strValue);
                //Khách hàng, địa chỉ, điện thoại
                dataRow = sheet.GetRow(4);
                dataCell = dataRow.GetCell(0);
                strValue = dataCell.ToString();
                strValue = String.Format(strValue, pLstOrder[0].FULLNAME, pLstOrder[0].ADDRESS, pLstOrder[0].PHONE);
                dataCell.SetCellValue(strValue);
                //Danh sách chi tiết đơn hàng
                int i = 6;
                foreach (Order order in pLstOrder)
                {                   
                    
                    dataRow = sheet.GetRow(i);
                    dataRow.CreateCell(0, CellType.STRING);
                    dataRow.CreateCell(1, CellType.STRING);
                    dataRow.CreateCell(2, CellType.STRING);
                    dataRow.CreateCell(3, CellType.STRING);
                    dataRow.CreateCell(4, CellType.STRING);
                    dataRow.CreateCell(5, CellType.STRING);
                    dataRow.CreateCell(6, CellType.STRING);
                    setThinBorderStyle(dataRow.GetCell(0), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(0).SetCellValue(i - 5);
                    setThinBorderStyle(dataRow.GetCell(1), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(1).SetCellValue(order.PRODUCT_DETAIL.NAME);
                    setThinBorderStyle(dataRow.GetCell(2), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(2).SetCellValue(order.PRODUCT_DETAIL.WEIGHT);
                    setThinBorderStyle(dataRow.GetCell(3), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(3).SetCellValue(order.PRODUCT_DETAIL.PRICE);
                    setThinBorderStyle(dataRow.GetCell(4), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(4).SetCellValue(order.QUANTITY);
                    setThinBorderStyle(dataRow.GetCell(5), templateWorkbook.CreateCellStyle());
                    setThinBorderStyle(dataRow.GetCell(6), templateWorkbook.CreateCellStyle());
                    dataRow.GetCell(6).SetCellValue(order.TOTAL);
                    if (i - 5 < pLstOrder.Count)
                    {
                        i = i + 1;
                        sheet.ShiftRows(i, sheet.LastRowNum, 1, true, false);
                        sheet.CreateRow(i);
                    }
                }
                // tính tổng
                dataRow = sheet.GetRow(i + 1);
                dataRow.GetCell(6).CellFormula = String.Format("SUM(G7:G{0})",i+1);
                //
                sheet.ForceFormulaRecalculation = true;
                //using (FileStream filedata = new FileStream(pFileOut, FileMode.Create))
                //{
                //    templateWorkbook.Write(filedata);
                //    filedata.Close();
                //}
                // Writing the workbook content to the FileStream...
                
                templateWorkbook.Write(output);
                //POIFSFileSystem poi = new POIFSFileSystem(ms);
               
                //fs.Close();
            }
            catch (Exception ex)
            {
                //TempData["Message"] = "Oops! Something went wrong.";

                //return RedirectToAction("NPOI");
            }

 
            return output ;
        }


        private static void setThinBorderStyle(ICell cell, ICellStyle style)
        {
            cell.SetCellValue("");
            style.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
            //style.setBottomBorderColor(IndexedColors.RED.getIndex());
            style.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
            //style.setLeftBorderColor(IndexedColors.RED.getIndex());
            style.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
            //style.setRightBorderColor(IndexedColors.RED.getIndex());
            style.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
            //style.setTopBorderColor(IndexedColors.RED.getIndex());
            cell.CellStyle = style;
        }
    }
}
